﻿
CREATE PROCEDURE [dbo].[proc_Customer_Getlist_Audit]
(
		@AuditPerson varchar(20),
		@CompanyId int,
		@Stext nvarchar(50)
)
As
Begin
If(@Stext='')	--关键词为空
	Begin
		Declare @NotAuditCount int,@OrAuditCount int,@MyAuditCount int

		--该公司未审核客户总数
		Select @NotAuditCount = Count(0) From Customer Where CompanyId=@CompanyId And Audit=0
		--该公司正在审核中的客户总数(暂未通过)
		Select @OrAuditCount = Count(0) From Customer Where CompanyId=@CompanyId And Audit=0 And AuditPerson Is Not Null

		If @OrAuditCount=@NotAuditCount		--都在进行中了，提取前15个
		Begin
			--自己正在审核的客户数
			Select @MyAuditCount = Count(0) From Customer Where CompanyId=@CompanyId And Audit=0 And AuditPerson=@AuditPerson
			If @MyAuditCount=0	--自己没有正在审核的客户(或上一批都已经审核完了)	
				Select Top 15 ROW_NUMBER() OVER(ORDER BY cus.CreateDate,cus.ProtectId DESC) as Row,
					Id,CustomerId,CustomerName,-1 As CusNameCount,MnemonicWorks,
					(Select Count(0) From Customer b Where b.CompanyId=@CompanyId and ((b.CustomerName like '%'+cus.MnemonicWorks+'%' And cus.MnemonicWorks<>'') Or b.MnemonicWorks=cus.MnemonicWorks) And b.Audit=1) As CusMnemCount,
					CreateName,CreateDate,	ProtectId,0 As OrdersCount,
					(Select top 1 EName From Employee Where Employee.UserName=cus.CreateName) As CreateNameEName,
					(case when (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) = '' then (Select OfficeTel From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) else (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) end) as Tel,
					(Select top 1 Title From Customer_Protect c Where c.Id=cus.ProtectId) As Protect,
					@NotAuditCount As RecordCount
					 From Customer cus Where CompanyId=@CompanyId And Audit=0
					 Order By Row
			Else	--提取自己审核中的15个用户(可能不到15个)
				Select Top 15 ROW_NUMBER() OVER(ORDER BY cus.CreateDate,cus.ProtectId DESC) as Row,
					Id,CustomerId,CustomerName,-1 As CusNameCount,MnemonicWorks,
					(Select Count(0) From Customer b Where b.CompanyId=@CompanyId and ((b.CustomerName like '%'+cus.MnemonicWorks+'%' And cus.MnemonicWorks<>'') Or b.MnemonicWorks=cus.MnemonicWorks) And b.Audit=1) As CusMnemCount,
					CreateName,CreateDate,	ProtectId,0 As OrdersCount,
					(Select top 1 EName From Employee Where Employee.UserName=cus.CreateName) As CreateNameEName,
					(case when (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) = '' then (Select OfficeTel From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) else (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) end) as Tel,
					(Select top 1 Title From Customer_Protect c Where c.Id=cus.ProtectId) As Protect,
					@NotAuditCount As RecordCount
					 From Customer cus Where CompanyId=@CompanyId And Audit=0 And AuditPerson=@AuditPerson
					 Order By Row
		End
		Else	--有尚未进行审核的客户
		Begin
			--自己正在审核的客户数
			Select @MyAuditCount = Count(0) From Customer Where CompanyId=@CompanyId And Audit=0 And AuditPerson=@AuditPerson
			If @MyAuditCount=0	--自己没有正在审核的客户(或上一批都已经审核完了)
			Begin
				--把前15个从未审核的客户转为自己审核(可能不到15个)
				Update a Set AuditPerson=@AuditPerson From Customer a,
				(Select Top 15 Id From Customer Where CompanyId=@CompanyId And Audit=0 And AuditPerson Is Null) b
				Where a.CompanyId=@CompanyId And a.Id=b.Id
			End

			--提取自己审核中的15个用户(可能不到15个)
			Select Top 15 ROW_NUMBER() OVER(ORDER BY cus.CreateDate,cus.ProtectId DESC) as Row,
				Id,CustomerId,CustomerName,-1 As CusNameCount,MnemonicWorks,
				(Select Count(0) From Customer b Where b.CompanyId=@CompanyId and ((b.CustomerName like '%'+cus.MnemonicWorks+'%' And cus.MnemonicWorks<>'') Or b.MnemonicWorks=cus.MnemonicWorks) And b.Audit=1) As CusMnemCount,
				CreateName,CreateDate,	ProtectId,0 As OrdersCount,
				(Select top 1 EName From Employee Where Employee.UserName=cus.CreateName) As CreateNameEName,
				(case when (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) = '' then (Select OfficeTel From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) else (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) end) as Tel,
				(Select top 1 Title From Customer_Protect c Where c.Id=cus.ProtectId) As Protect,
				@NotAuditCount As RecordCount
				 From Customer cus Where CompanyId=@CompanyId And Audit=0 And AuditPerson=@AuditPerson
				 Order By Row
		End
--		Select top 15 ROW_NUMBER() OVER(ORDER BY cus.CreateDate,cus.ProtectId DESC) as Row,
--		Id,CustomerId,
--		CustomerName,
--		(Select Count(0) From Customer a Where a.CompanyId=@CompanyId and a.CustomerName=cus.CustomerName And a.Audit=1) As CusNameCount,
--		-1 As CusNameCount,
--		MnemonicWorks,
--		(case when (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) = '' then (Select OfficeTel From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) else (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) end) as Tel,
--		(Select Count(0) From Customer b Where b.CompanyId=@CompanyId and ((b.CustomerName like '%'+cus.MnemonicWorks+'%' And cus.MnemonicWorks<>'') Or b.MnemonicWorks=cus.MnemonicWorks) And b.Audit=1) As CusMnemCount,
--		CreateName,
--		(Select top 1 EName From Employee Where Employee.UserName=cus.CreateName) As CreateNameEName,
--		CreateDate,
--		ProtectId,
--		(Select top 1 Title From Customer_Protect c Where c.Id=cus.ProtectId) As Protect,
--		(Select Count(0) From [Order] Where [Order].CusId=cus.CustomerId) as OrdersCount,
--0 As OrdersCount,
--		(Select count(0) from Customer f Where f.CompanyId=@CompanyId and f.Audit=0) As RecordCount
--		 From Customer cus Where CompanyId=@CompanyId and Audit=0
--		 ORDER BY Row
	End
Else
	Select top 15 ROW_NUMBER() OVER(ORDER BY cus.CreateDate,cus.ProtectId DESC) as Row,
	Id,CustomerId,CustomerName,-1 As CusNameCount,MnemonicWorks,
	(Select Count(0) From Customer a Where a.CompanyId=@CompanyId and a.CustomerName=cus.CustomerName And a.Audit=1) As CusNameCount,
	(case when (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) = '' then (Select OfficeTel From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) else (Select Mobile From ContactPerson g Where g.CustomerId=cus.CustomerId and g.IsMain=1) end) as Tel,
	(Select Count(0) From Customer b Where b.CompanyId=@CompanyId and ((b.CustomerName like '%'+cus.MnemonicWorks+'%' And cus.MnemonicWorks<>'') Or b.MnemonicWorks=cus.MnemonicWorks) And b.Audit=1) As CusMnemCount,
	CreateName,CreateDate,ProtectId,0 As OrdersCount,
	(Select top 1 EName From Employee Where Employee.UserName=cus.CreateName) As CreateNameEName,
	(Select top 1 Title From Customer_Protect c Where c.Id=cus.ProtectId) As Protect,
--		(Select Count(0) From [Order] Where [Order].CusId=cus.CustomerId) as OrdersCount,
	(Select count(0) from Customer f Where f.CompanyId=@CompanyId and f.Audit=0) As RecordCount
	 From Customer cus Where CompanyId=@CompanyId and Audit=0 and cus.CustomerName like '%'+@Stext+'%'
	 ORDER BY Row
END